﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoyoEden.Models;
using System.Data.Common;
using System.Data;

namespace CoyoEden.Providers
{
	public partial class DbPortalProvider
	{
        public override Vote SelectVote(int id)
		{
			Vote item = new Vote();
			using (DbConnection conn = MyDbFactory.CreateConnection())
			{//using1
				conn.ConnectionString = connStringStatic;
				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("SELECT Id, Title,Description,Author,DateCreated,DateModified,IsPublished,IsMultiple FROM {0}Votes WHERE Id={1}id;", tablePrefix, parmPrefix);
					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = id;
					cmd.Parameters.Add(dpID);

					conn.Open();
					//1,load basic info
					using (DbDataReader rdr = cmd.ExecuteReader())
					{//using2
						if (rdr.HasRows)
						{
							rdr.Read();
							item.Id = rdr.GetInt32(0);
							item.Title = rdr.GetString(1);
							item.Description = rdr.GetString(2);
							item.Author = rdr.GetString(3);
							item.DateCreated = rdr.GetDateTime(4);
							item.DateModified= rdr.GetDateTime(5);
							item.IsPublished = rdr.GetBoolean(6);
							item.IsMultiple = rdr.GetBoolean(7);
						}

					}//endof using2

					//2.load voteitems
					LoadVoteItems(item,conn);
				}
			}//endof using1
			return item;
		}

        public override void InsertVote(Vote item)
		{
			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				using (DbCommand cmd = conn.CreateCommand())
				{
					conn.Open();
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("INSERT INTO {0}Votes (Title,Description,Author,DateCreated,DateModified,IsPublished,IsMultiple) Values ({1}title,{1}desc,{1}author,{1}created,{1}modified,{1}published,{1}multiple);", tablePrefix, parmPrefix);
					sql.AppendFormat(" SELECT Id FROM {0}Votes WHERE Title={1}title AND Description={1}desc AND Author={1}author;", tablePrefix, parmPrefix);

					cmd.CommandType = CommandType.Text;
					cmd.CommandText = sql.ToString();

					DbParameter dpTitle = MyDbFactory.CreateParameter();
					dpTitle.ParameterName = parmPrefix + "title";
					dpTitle.Value = item.Title;
					cmd.Parameters.Add(dpTitle);

					DbParameter dpDesc = MyDbFactory.CreateParameter();
					dpDesc.ParameterName = parmPrefix + "desc";
					dpDesc.Value = item.Description;
					cmd.Parameters.Add(dpDesc);

					DbParameter dpAuthor = MyDbFactory.CreateParameter();
					dpAuthor.ParameterName = parmPrefix + "author";
					dpAuthor.Value = item.Author;
					cmd.Parameters.Add(dpAuthor);

					DbParameter dpCreated = MyDbFactory.CreateParameter();
					dpCreated.ParameterName = parmPrefix + "created";
					dpCreated.Value = DateTime.Now;
					cmd.Parameters.Add(dpCreated);

					DbParameter dpModified = MyDbFactory.CreateParameter();
					dpModified.ParameterName = parmPrefix + "modified";
					dpModified.Value = DateTime.Now;
					cmd.Parameters.Add(dpModified);

					DbParameter dpPublished = MyDbFactory.CreateParameter();
					dpPublished.ParameterName = parmPrefix + "published";
					dpPublished.Value = item.IsPublished;
					cmd.Parameters.Add(dpPublished);

					DbParameter dpMultiple = MyDbFactory.CreateParameter();
					dpMultiple.ParameterName = parmPrefix + "multiple";
					dpMultiple.Value = item.IsMultiple;
					cmd.Parameters.Add(dpMultiple);

					var id = cmd.ExecuteScalar();
					item.Id = Convert.ToInt32(id);

				}
			}// /using
			List<Vote> items = Vote.Votes;
			items.Add(item);
		}

        public override void UpdateVote(Vote item)
		{
			List<Vote> items = Vote.Votes;
			items.Remove(item);
			items.Add(item);

			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				using (DbCommand cmd = conn.CreateCommand())
				{
					conn.Open();
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("Update {0}Votes SET Title={1}title,Description={1}desc,Author={1}author,DateCreated={1}created,DateModified={1}modified,IsPublished={1}published,IsMultiple={1}multiple WHERE Id={1}id;", tablePrefix, parmPrefix);

					cmd.CommandType = CommandType.Text;
					cmd.CommandText = sql.ToString();

					DbParameter dpTitle = MyDbFactory.CreateParameter();
					dpTitle.ParameterName = parmPrefix + "title";
					dpTitle.Value = item.Title;
					cmd.Parameters.Add(dpTitle);

					DbParameter dpDesc = MyDbFactory.CreateParameter();
					dpDesc.ParameterName = parmPrefix + "desc";
					dpDesc.Value = item.Description;
					cmd.Parameters.Add(dpDesc);

					DbParameter dpAuthor = MyDbFactory.CreateParameter();
					dpAuthor.ParameterName = parmPrefix + "author";
					dpAuthor.Value = item.Author;
					cmd.Parameters.Add(dpAuthor);

					DbParameter dpCreated = MyDbFactory.CreateParameter();
					dpCreated.ParameterName = parmPrefix + "created";
					dpCreated.Value = DateTime.Now;
					cmd.Parameters.Add(dpCreated);

					DbParameter dpModified = MyDbFactory.CreateParameter();
					dpModified.ParameterName = parmPrefix + "modified";
					dpModified.Value = DateTime.Now;
					cmd.Parameters.Add(dpModified);

					DbParameter dpPublished = MyDbFactory.CreateParameter();
					dpPublished.ParameterName = parmPrefix + "published";
					dpPublished.Value = item.IsPublished;
					cmd.Parameters.Add(dpPublished);

					DbParameter dpMultiple = MyDbFactory.CreateParameter();
					dpMultiple.ParameterName = parmPrefix + "multiple";
					dpMultiple.Value = item.IsMultiple;
					cmd.Parameters.Add(dpMultiple);

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = item.Id;
					cmd.Parameters.Add(dpID);

					cmd.ExecuteNonQuery();

					//1,Update voteitems
					UpdateVoteItems(item, conn);
				}
			}
		}

        public override void DeleteVote(Vote item)
		{
			List<Vote> items = Vote.Votes;
			items.Remove(item);

			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					StringBuilder sql = new StringBuilder();
					sql.AppendFormat("DELETE FROM {0}VoteItem WHERE VoteID = {1}id;", tablePrefix, parmPrefix);
					sql.AppendFormat("DELETE FROM {0}Votes WHERE Id = {1}id;", tablePrefix, parmPrefix);

					cmd.CommandText = sql.ToString();
					cmd.CommandType = CommandType.Text;

					DbParameter dpID = MyDbFactory.CreateParameter();
					dpID.ParameterName = parmPrefix + "id";
					dpID.Value = item.Id;
					cmd.Parameters.Add(dpID);

					cmd.ExecuteNonQuery();
				}
				//2,delete vote items
				item.VoteItems.ForEach(obj => {
					DeleteRate(obj, conn, tablePrefix, parmPrefix);
				});
			}
		}

        public override List<Vote> FillVotes()
		{
			List<Vote> votes = new List<Vote>();
			List<int> voteIDs = new List<int>();

			using (DbConnection conn = MyDbFactory.CreateConnection())
			{
				conn.ConnectionString = connStringStatic;

				using (DbCommand cmd = conn.CreateCommand())
				{
					string sqlQuery = "SELECT Id FROM " + tablePrefix + "Votes;";
					cmd.CommandText = sqlQuery;
					cmd.CommandType = CommandType.Text;

					conn.Open();

					using (DbDataReader rdr = cmd.ExecuteReader())
					{
						while (rdr.Read())
						{
							voteIDs.Add(rdr.GetInt32(0));
						}
					}
				}
			}

			foreach (int id in voteIDs)
			{
				votes.Add(Vote.Load(id));
			}

			//votes.Sort();
			return votes;
		}

		#region helper methods
		private void LoadVoteItems(Vote item, DbConnection conn)
		{
			var itemIDs = new List<int>();
			using(DbCommand cmd=conn.CreateCommand()){
				StringBuilder sql = new StringBuilder();
				sql.AppendFormat("SELECT DISTINCT RateID FROM {0}VoteItem WHERE VoteID={1}id;",tablePrefix,parmPrefix);
				cmd.CommandText = sql.ToString();
				cmd.CommandType = CommandType.Text;

				DbParameter dpID = MyDbFactory.CreateParameter();
				dpID.ParameterName = parmPrefix + "id";
				dpID.Value = item.Id;
				cmd.Parameters.Add(dpID);

				using(DbDataReader rdr=cmd.ExecuteReader()){//using2
					while (rdr.Read()) {
						itemIDs.Add(rdr.GetInt32(0));
					}
				}//endof using2

				//2.load voteitems
				itemIDs.ForEach(id => {
					item.VoteItems.Add(SelectRate(id,conn,tablePrefix,parmPrefix));
				});
			}
		}
		private void UpdateVoteItems(Vote item, DbConnection conn)
		{
			using(DbCommand cmd=conn.CreateCommand()){
				cmd.CommandType = CommandType.Text;
				StringBuilder sql = new StringBuilder();
				sql.AppendFormat("DELETE FROM {0}VoteItem WHERE VoteID={1}id;",tablePrefix,parmPrefix);
				cmd.CommandText = sql.ToString();

				DbParameter dpID = MyDbFactory.CreateParameter();
				dpID.ParameterName = parmPrefix + "id";
				dpID.Value = item.Id;
				cmd.Parameters.Add(dpID);

				cmd.ExecuteNonQuery();

				sql.Replace(sql.ToString(),"");
				sql.AppendFormat("INSERT INTO {0}VoteItem (VoteID,RateID) Values ({1}id,{1}rid);",tablePrefix,parmPrefix);
				cmd.CommandText = sql.ToString();

				DbParameter dpRateID = MyDbFactory.CreateParameter();
				dpRateID.ParameterName = parmPrefix + "rid";
				cmd.Parameters.Add(dpRateID);

				item.VoteItems.ForEach(obj => {
					dpRateID.Value = obj.Id;
					cmd.ExecuteNonQuery();
				});
			}
		}
		#endregion
	}
}
